using System;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;
using System.Collections;
using System.Collections.Specialized;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;

using Deepcode.Flux.Core.Data;
using Deepcode.Flux.Core.Services.FileSystem;

public partial class Private_Config_SQL_Default : Deepcode.Flux.Core.UI.AdministrationModulePage
{
	#region Permission Code Override
	public override string RequiredPermissionCode { get { return "FluxSQL"; } }
	#endregion
	#region Page Load event
	/// <summary>
	/// Page Load event
	/// </summary>
	/// <param name="sender"></param>
	/// <param name="e"></param>
	protected void Page_Load(object sender, EventArgs e)
	{
		if (!Page.IsPostBack)
		{
			input.Text = UserSetting("LASTSQLQUERY", "");
			chkBatch.Checked = UserSetting("LASTSQLBATCHCHECK", true);
			SizeInput();
		}
	}
	#endregion
	#region User interactions
	/// <summary>
	/// User clicked the add new button
	/// </summary>
	/// <param name="sender"></param>
	/// <param name="e"></param>
	protected void btnBigger_Click(object sender, EventArgs e)
	{
		bool isBig = this.UserSetting("LASTSQLBIGGER", false );
		this.SetUserSetting( "LASTSQLBIGGER", !isBig );

		SizeInput();
	}
	/// <summary>
	/// User clicked the add new button
	/// </summary>
	/// <param name="sender"></param>
	/// <param name="e"></param>
	protected void btnExecute_Click(object sender, EventArgs e)
	{
		secData.Visible = false;
		secOutput.Visible = false;

		output.Text = "";
		datagrid.DataSource = null;
		datagrid.DataBind();

		// Store last command to registry
		this.SetUserSetting("LASTSQLQUERY", input.Text);
		this.SetUserSetting("LASTSQLBATCHCHECK", chkBatch.Checked);

		if (input.Text.StartsWith("~/"))
		{
			if( chkBatch.Checked ) ExecuteDirectorySafe();
			else ExecuteDirectory();
			return;
		}
		else
			ExecuteCommand();
	}
	#endregion
	#region Utility
	/// <summary>
	/// Sets up the UI depending on current state
	/// </summary>
	protected void SizeInput()
	{
		bool isBig = UserSetting("LASTSQLBIGGER", false );
		if (isBig)
		{
			input.Height = new Unit("400");
			btnBigger.Text = "Smaller";
		}
		else
		{
			input.Height = new Unit("100");
			btnBigger.Text = "Bigger";
		}
	}
	#endregion
	#region Execute COMMAND
	/// <summary>
	/// Executes the input text directly on the SQL connection
	/// </summary>
	private void ExecuteCommand()
	{
		SqlConnection conn = null;
		SqlTransaction tran = null;
		SqlDataAdapter cmd = null;
		DataTable data = new DataTable();
		try
		{
			conn = new SqlConnection(Deepcode.Flux.Core.Services.Configuration.FluxConfig.MainDSN);
			conn.Open();
			if( chkBatch.Checked ) tran = conn.BeginTransaction();

			cmd = new SqlDataAdapter(input.Text, conn);
			cmd.SelectCommand.CommandType = CommandType.Text;
			cmd.SelectCommand.Transaction = tran;
			cmd.Fill(data);

			if( tran != null ) tran.Commit();
			conn.Close();
		}
		catch (Exception ex)
		{
			try{ if( tran != null ) tran.Rollback(); } catch {};
			try{ if( conn != null ) conn.Close(); } catch {};

			secOutput.Visible = true;
			output.Text = "<span class='dangerlink'>";
			output.Text += ex.Message;
			output.Text += "<br/>" + ex.Source;
			output.Text += "</span>";
			output.Text += "<blockquote>" + ex.StackTrace.Replace("\n", "<br/>") + "</blockquote>";
			return;
		}
		if (data.Columns.Count > 0)
		{
			secData.Visible = true;
			datagrid.DataSource = data;
			datagrid.DataBind();
		}
		else
		{
			output.Text = "<span class='safelink'>Success</span>";
			secOutput.Visible = true;
		}
	}
	#endregion
	#region Execute DIRECTORY (Non Transaction)
	/// <summary>
	/// Execute any .SQL files within a particular directory
	/// </summary>
	private void ExecuteDirectory()
	{
		output.Text = "";
		// Search for files
		FileScanService fs = new FileScanService();
		StringCollection files = fs.SearchFilesInWebPath( input.Text, "*.sql", true );
		
		// Return if none found
		if( files.Count < 1 )
		{
			secOutput.Visible = true;
			output.Text += "<span class='dangerlink'>";
			output.Text += "No .sql files found to process";
			output.Text += "</span>";
			return;
		}
		
		// Open the connection
		SqlConnection c = null;
		try
		{
			c = new SqlConnection(Deepcode.Flux.Core.Services.Configuration.FluxConfig.MainDSN);
			c.Open();
		}
		catch( Exception e )
		{
			try{ if( c != null ) c.Close(); }catch{}
			secOutput.Visible = true;
			output.Text += "<span class='dangerlink'>";
			output.Text += e.Message;
			output.Text += "<br/>" + e.Source;
			output.Text += "</span>";
			output.Text += "<blockquote>" + e.StackTrace.Replace("\n", "<br/>") + "</blockquote>";
			return;
		}

		// Process each file one at a time
		secOutput.Visible = true;
		SQLBatchProcessor sb = new SQLBatchProcessor();

		string startSuccess = "<div style='padding: 5 5 5 5; border-bottom: 1px solid silver; background-color:#d1fad3;'>";
		string startError = "<div style='padding: 5 5 5 5; border-bottom: 1px solid silver; background-color:#f3c5bc;'>";
		string endSuccess = "</div>";
		string endError = "</div>";
		output.Text += "<div style='border-top: 1px solid silver;border-left: 1px solid silver;border-right: 1px solid silver;'>";
		foreach (string f in files)
		{
			try
			{
				sb.ExecuteQueryScriptSQLFile(f, c, null);
				output.Text += startSuccess + f + endSuccess;
			}
			catch (Exception e)
			{
				output.Text += startError + f + "<br/>" + e.Message + endError;
			}
		}

		// Close the connection
		try
		{
			c.Close();
		}
		catch (Exception e)
		{
			try { if (c != null) c.Close(); }
			catch { }
			secOutput.Visible = true;
			output.Text += "<span class='dangerlink'>";
			output.Text += e.Message;
			output.Text += "<br/>" + e.Source;
			output.Text += "</span>";
			output.Text += "<blockquote>" + e.StackTrace.Replace("\n", "<br/>") + "</blockquote>";
			return;
		}
	}
	#endregion
	#region Execute DIRECTORY (With transaction)
	/// <summary>
	/// Execute any .SQL files within a particular directory
	/// </summary>
	private void ExecuteDirectorySafe()
	{
		output.Text = "";
		// Search for files
		FileScanService fs = new FileScanService();
		StringCollection files = fs.SearchFilesInWebPath(input.Text, "*.sql", true);

		// Return if none found
		if (files.Count < 1)
		{
			secOutput.Visible = true;
			output.Text += "<span class='dangerlink'>";
			output.Text += "No .sql files found to process";
			output.Text += "</span>";
			return;
		}

		// Open the connection and transaction
		SqlConnection c = null;
		SqlTransaction t = null;
		try
		{
			c = new SqlConnection(Deepcode.Flux.Core.Services.Configuration.FluxConfig.MainDSN);
			c.Open();
			t = c.BeginTransaction();

			// Process each file one at a time
			secOutput.Visible = true;
			SQLBatchProcessor sb = new SQLBatchProcessor();

			foreach (string f in files)
			{
				output.Text += "<br/>" + f;
				sb.ExecuteQueryScriptSQLFile(f, c, t);
			}

			t.Commit();
			c.Close();
		}
		catch (Exception e)
		{
			try { if (c != null) c.Close(); }
			catch { }
			secOutput.Visible = true;
			output.Text += "<br/><br/><span class='dangerlink'>";
			output.Text += e.Message;
			output.Text += "<br/>" + e.Source;
			output.Text += "</span>";
			output.Text += "<blockquote>" + e.StackTrace.Replace("\n", "<br/>") + "</blockquote>";
			return;
		}
	}
	#endregion
}
